﻿/********************************************************
 * 类名： InitPurDAL.cs
 * 功能： 购货单数据层
 *   ver	变更日		作者			变更内容
 * ────────────────────────────────────────
 *   V01.0	2013-12-20	李波			初版
 *
 * Copyright (c) 2013，李波版权所有
 ********************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using PSS.Entity.InitPurManage;
using PSS.Utility;

namespace PSS.DAL.InitPurManage
{
    public class InitPurDAL : DbHelperBase
    {
        public bool AddInitPur(InitPurEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [PSS_InitPur]([InitPurId],[VendorId],[DocDate],[DocNumber],[BusinessCategory],[Remark],[DiscountRate],[DeductionAmount],[FoldAmount],[Payment],[AccountId],[Arrears]) ");
            strSql.Append(" VALUES(@InitPurId,@VendorId,@DocDate,@DocNumber,@BusinessCategory,@Remark,@DiscountRate,@DeductionAmount,@FoldAmount,@Payment,@AccountId,@Arrears) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@InitPurId", model.InitPurId);
                    Db.AddInParameter(cmd, "@VendorId", model.VendorId);
                    Db.AddInParameter(cmd, "@DocDate", model.DocDate);
                    Db.AddInParameter(cmd, "@DocNumber", model.DocNumber);
                    Db.AddInParameter(cmd, "@BusinessCategory", model.BusinessCategory);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    Db.AddInParameter(cmd, "@DiscountRate", model.DiscountRate);
                    Db.AddInParameter(cmd, "@DeductionAmount", model.DeductionAmount);
                    Db.AddInParameter(cmd, "@FoldAmount", model.FoldAmount);
                    Db.AddInParameter(cmd, "@Payment", model.Payment);
                    Db.AddInParameter(cmd, "@AccountId", model.AccountId);
                    Db.AddInParameter(cmd, "@Arrears", model.Arrears);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public int GetTotal(InitPurQcEntity qcmodel)
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) AS [Total] FROM PSS_InitPur AS A  ");
            strSql.Append(" INNER JOIN PSS_Vendor AS B ON A.VendorId=B.VendorId ");
            strSql.Append(" LEFT JOIN (SELECT InitPurId,SUM(PurchaseAmount) AS TotalPurchaseAmount FROM PSS_InitPurDetail ");
            strSql.Append(" GROUP BY InitPurId) AS C ON A.InitPurId=C.InitPurId ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.DocNumber))
            {
                strSql.Append(" AND A.DocNumber LIKE '%" + qcmodel.DocNumber + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.VendorId))
            {
                strSql.Append(" AND A.VendorId=@VendorId ");
            }
            if (!string.IsNullOrEmpty(qcmodel.DocDateFrom))
            {
                strSql.Append(" AND A.DocDate >=@SDocDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.DocDateEnd))
            {
                strSql.Append(" AND A.DocDate <=@EDocDate");
            }
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.DocDateFrom))
                    {
                        Db.AddInParameter(cmd, "@SDocDate", DbType.DateTime, Convert.ToDateTime(qcmodel.DocDateFrom));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.DocDateEnd))
                    {
                        Db.AddInParameter(cmd, "@EDocDate", DbType.DateTime, Convert.ToDateTime(qcmodel.DocDateEnd));
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<InitPurEntity> GetInitPurList(InitPurQcEntity qcmodel)
        {
            var list = new List<InitPurEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by A.DocNumber desc) AS idx,A.InitPurId,A.DocDate,A.DocNumber,A.BusinessCategory,B.VendorName,C.TotalPurchaseAmount,A.FoldAmount,A.Payment FROM PSS_InitPur AS A  ");
            strSql.Append(" INNER JOIN PSS_Vendor AS B ON A.VendorId=B.VendorId ");
            strSql.Append(" LEFT JOIN (SELECT InitPurId,SUM(PurchaseAmount) AS TotalPurchaseAmount FROM PSS_InitPurDetail ");
            strSql.Append(" GROUP BY InitPurId) AS C ON A.InitPurId=C.InitPurId ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.DocNumber))
            {
                strSql.Append(" AND A.DocNumber LIKE '%" + qcmodel.DocNumber + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.VendorId))
            {
                strSql.Append(" AND A.VendorId=@VendorId ");
            }
            if (!string.IsNullOrEmpty(qcmodel.DocDateFrom))
            {
                strSql.Append(" AND A.DocDate >=@SDocDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.DocDateEnd))
            {
                strSql.Append(" AND A.DocDate <=@EDocDate");
            }
            strSql.Append(") AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.DocDateFrom))
                    {
                        Db.AddInParameter(cmd, "@SDocDate", DbType.DateTime, Convert.ToDateTime(qcmodel.DocDateFrom));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.DocDateEnd))
                    {
                        Db.AddInParameter(cmd, "@EDocDate", DbType.DateTime, Convert.ToDateTime(qcmodel.DocDateEnd));
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new InitPurEntity();
                            if (reader["InitPurId"] != DBNull.Value)
                                model.InitPurId = new Guid(reader["InitPurId"].ToString());
                            if (reader["DocDate"] != DBNull.Value)
                                model.DocDate = Convert.ToDateTime(reader["DocDate"].ToString());
                            model.DocNumber = reader["DocNumber"] == obj ? null : Convert.ToString(reader["DocNumber"]);
                            model.VendorName = reader["VendorName"] == obj ? null : Convert.ToString(reader["VendorName"]);
                            if (reader["BusinessCategory"] != DBNull.Value)
                                model.BusinessCategory = Convert.ToInt32(reader["BusinessCategory"].ToString());
                            if (reader["TotalPurchaseAmount"] != DBNull.Value)
                                model.PurchaseAmount = Convert.ToDecimal(reader["TotalPurchaseAmount"].ToString());
                            if (reader["FoldAmount"] != DBNull.Value)
                                model.FoldAmount = Convert.ToDecimal(reader["FoldAmount"].ToString());
                            if (reader["Payment"] != DBNull.Value)
                                model.Payment = Convert.ToDecimal(reader["Payment"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public bool ModifyInitPur(InitPurEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [PSS_InitPur] SET [VendorId] = @VendorId,[DocDate] = @DocDate,[DocNumber] = @DocNumber,[BusinessCategory] = @BusinessCategory,[Remark] = @Remark,[DiscountRate] = @DiscountRate,[DeductionAmount] = @DeductionAmount,[FoldAmount] = @FoldAmount,[Payment] = @Payment,[AccountId] = @AccountId,[Arrears] = @Arrears WHERE [InitPurId] = @InitPurId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@VendorId", model.VendorId);
                    Db.AddInParameter(cmd, "@DocDate", model.DocDate);
                    Db.AddInParameter(cmd, "@DocNumber", model.DocNumber);
                    Db.AddInParameter(cmd, "@BusinessCategory", model.BusinessCategory);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    Db.AddInParameter(cmd, "@DiscountRate", model.DiscountRate);
                    Db.AddInParameter(cmd, "@DeductionAmount", model.DeductionAmount);
                    Db.AddInParameter(cmd, "@FoldAmount", model.FoldAmount);
                    Db.AddInParameter(cmd, "@Payment", model.Payment);
                    Db.AddInParameter(cmd, "@AccountId", model.AccountId);
                    Db.AddInParameter(cmd, "@Arrears", model.Arrears);
                    Db.AddInParameter(cmd, "@InitPurId", model.InitPurId);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool RemoveInitPur(string id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [PSS_InitPur] WHERE InitPurId=@InitPurId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@InitPurId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public InitPurEntity GetInitPurInfo(string id)
        {
            var model = new InitPurEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [InitPurId],[VendorId],[DocDate],[DocNumber],[BusinessCategory],[Remark],[DiscountRate],[DeductionAmount],[FoldAmount],[Payment],[AccountId],[Arrears] ");
            strSql.Append(" FROM [PSS_InitPur] WHERE InitPurId=@InitPurId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@InitPurId", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["InitPurId"] != DBNull.Value)
                                model.InitPurId = new Guid(reader["InitPurId"].ToString());
                            if (reader["VendorId"] != DBNull.Value)
                                model.VendorId = new Guid(reader["VendorId"].ToString());
                            if (reader["DocDate"] != DBNull.Value)
                                model.DocDate = Convert.ToDateTime(reader["DocDate"].ToString());
                            model.DocNumber = reader["DocNumber"] == obj ? null : Convert.ToString(reader["DocNumber"]);
                            if (reader["BusinessCategory"] != DBNull.Value)
                                model.BusinessCategory = Convert.ToInt32(reader["BusinessCategory"].ToString());
                            model.Remark = reader["Remark"] == obj ? null : Convert.ToString(reader["Remark"]);
                            if (reader["DiscountRate"] != DBNull.Value)
                                model.DiscountRate = Convert.ToDecimal(reader["DiscountRate"].ToString());
                            if (reader["DeductionAmount"] != DBNull.Value)
                                model.DeductionAmount = Convert.ToDecimal(reader["DeductionAmount"].ToString());
                            if (reader["FoldAmount"] != DBNull.Value)
                                model.FoldAmount = Convert.ToDecimal(reader["FoldAmount"].ToString());
                            if (reader["Payment"] != DBNull.Value)
                                model.Payment = Convert.ToDecimal(reader["Payment"].ToString());
                            if (reader["AccountId"] != DBNull.Value)
                                model.AccountId = new Guid(reader["AccountId"].ToString());
                            if (reader["Arrears"] != DBNull.Value)
                                model.Arrears = Convert.ToDecimal(reader["Arrears"].ToString());
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public decimal GetTotalInitialNumber(string goodsId, string storageId)
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT B.InitialNumber,C.InventoryQty FROM PSS_Goods AS A ");
            strSql.Append(" LEFT JOIN PSS_GoodsDetail AS B ON A.GoodsId=B.GoodsId  ");
            strSql.Append(" LEFT JOIN (SELECT GoodsId,StorageId,SUM(InventoryQty) AS InventoryQty FROM PSS_Inventory WHERE StorageId=@StorageId AND GoodsId=@GoodsId AND InventoryType=1 group by GoodsId,StorageId ) ");
            strSql.Append(" AS C ON A.GoodsId=C.GoodsId AND B.StorageId=C.StorageId ");
            strSql.Append(" WHERE B.StorageId=@StorageId AND A.GoodsId=@GoodsId ");
            decimal qty = 0;
            object obj = DBNull.Value;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@GoodsId", goodsId);
                    Db.AddInParameter(cmd, "@StorageId", storageId);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            var initialNumber = reader["InitialNumber"] == obj ? 0 : Convert.ToDecimal(reader["InitialNumber"]);
                            var inventoryQty = reader["InventoryQty"] == obj ? 0 : Convert.ToDecimal(reader["InventoryQty"]);
                            qty = initialNumber + inventoryQty;
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return qty;
            }
        }
    }
}
